package kz.kstu.scantest.business.client.excel;

import kz.kstu.scantest.business.common.exception.LogicException;
import kz.kstu.scantest.business.entity.data.SubjectEnum;
import kz.kstu.scantest.business.entity.test.Question;
import kz.kstu.scantest.business.entity.test.SubjectQuestion;
import kz.kstu.scantest.business.entity.test.Test;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.*;

/**
 * @author Filipp_Stankevich
 *         created: 11.09.11 21:25
 */
public class TestExcelImporter {

    private final static Logger LOGGER = Logger.getLogger(TestExcelImporter.class);

    private static final int FIRST_SUBJECT_ROW_INDEX = 2;
    private static final int LAST_SUBJECT_ROW_INDEX = 14;
    private static final int FIRST_QUESTION_COLUMN_INDEX = 2;
    private static final int LAST_QUESTION_COLUMN_INDEX = 26;
    private static final int VARIANT_ROW_INDEX = 2;
    private static final int VARIANT_COLUMN_INDEX = 0;

    private final static List<String> AVAILABLE_ANSWERS = Arrays.asList("A", "B", "C", "D", "E");
    public static final int SUBJECT_NAME_COLUMN_INDEX = 1;
    public static final int VARIANT_LENGTH = 4;


    public Collection<Test> process(File file) throws IOException, LogicException {
        FileInputStream inputStream = new FileInputStream(file);
        Workbook workbook = new HSSFWorkbook(inputStream);
        return processWorkbook(workbook);
    }

    private Collection<Test> processWorkbook(Workbook workbook) throws LogicException {
        Set<Test> tests = new HashSet<Test>();
        int sheetCount = workbook.getNumberOfSheets();
        for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++) {
            Sheet sheet = workbook.getSheetAt(sheetIndex);

            Test test = new Test();
            Row variantRow = sheet.getRow(VARIANT_ROW_INDEX);

            if (variantRow == null) {
                LOGGER.error("Variant row not found. Sheet number " + (sheetIndex + 1));
                throw new LogicException("Строка с вариантом не найдена. Номер листа: {}", (sheetIndex + 1));
            }

            Cell variantCell = variantRow.getCell(VARIANT_COLUMN_INDEX);

            if (variantCell.getCellType() != Cell.CELL_TYPE_NUMERIC) {
                LOGGER.error("Variant not processed. Error variant number = " + variantCell.toString());
                throw new LogicException("Некорректный формат варианта: {}. Номер листа: {}",
                        variantCell.toString(), (sheetIndex + 1));
            }

            Integer variantCelIntValue = (int) variantCell.getNumericCellValue();

            String variant = variantCelIntValue.toString();

            int delta =  VARIANT_LENGTH - variant.length();

            for (int i = 0; i < delta; i++) {
                variant = '0' + variant;
            }

            test.setVariant(variant);

            if (tests.contains(test)) {
                LOGGER.error("Variant not processed. Duplicate variant number = " + variantCell.toString());
                throw new LogicException("Повторящийся номер варианта: {}. Номер листа: {}",
                        variant, (sheetIndex + 1));
            }

            LOGGER.info("Process variant = " + variant);

            for (int rowIndex = FIRST_SUBJECT_ROW_INDEX; rowIndex <= LAST_SUBJECT_ROW_INDEX; rowIndex++) {
                SubjectQuestion subjectQuestion = new SubjectQuestion();
                Row row = sheet.getRow(rowIndex);

                if (row == null) {
                    LOGGER.error("Variant not processed. Error subject name, variant = " + variantCell.toString());
                    throw new LogicException("Строка с предметом не найдена. Вариант: {}, номер строки: {}",
                            (rowIndex + 1), variant);
                }

                Cell subjectNameCell = row.getCell(SUBJECT_NAME_COLUMN_INDEX);

                if (subjectNameCell.getCellType() != Cell.CELL_TYPE_STRING) {
                    LOGGER.error("Variant not processed. Error subject name, variant = " + variantCell.toString());
                    throw new LogicException("Некорректный формат названия предмета: {}. Вариант: {}",
                            subjectNameCell.toString(), variant);
                }

                String subjectName = subjectNameCell.getStringCellValue();
                SubjectEnum subjectEnum = getSubjectByName(subjectName);

                if (subjectEnum == null) {
                    LOGGER.error("Variant not processed. Error subject name, variant = " + variantCell.toString());
                    throw new LogicException("Неивестное название предмета: {}. Вариант: {}",
                            subjectNameCell.toString(), variant);
                }

                subjectQuestion.setSubject(subjectEnum);
                int questionNumber = 1;
                for (int columnIndex = FIRST_QUESTION_COLUMN_INDEX; columnIndex <= LAST_QUESTION_COLUMN_INDEX; columnIndex++) {
                    Cell answerCell = row.getCell(columnIndex);

                    if (answerCell.getCellType() != Cell.CELL_TYPE_STRING) {
                        LOGGER.error("Variant not processed. Incorrect answer type, variant = " + variantCell.toString());
                        throw new LogicException("Неверный формат ответа: {}. Вариант: {}, предмет: {}, вопрос: {}",
                                answerCell.toString(), variant, subjectName, questionNumber);
                    }

                    String answerCellValue = answerCell.getStringCellValue();
                    String answer = answerCellValue.trim().toUpperCase();

                    if (!AVAILABLE_ANSWERS.contains(answer)) {
                        LOGGER.error("Variant not processed. Incorrect answer value, variant = " + variantCell.toString());
                        throw new LogicException("Неивестное значение ответа: {}. Вариант: {}, предмет: {}, вопрос: {}",
                                answerCell.toString(), variant, subjectName, questionNumber);
                    }

                    Question question = new Question();
                    question.setQuestionNumber(questionNumber++);
                    question.setRightAnswer(answer);
                    subjectQuestion.addQuestion(question);

                }
                test.addSubjectQuestion(subjectQuestion);
            }
            tests.add(test);
        }
        return tests;
    }

    private SubjectEnum getSubjectByName(String subjectName) {
        for (SubjectEnum subjectEnum : SubjectEnum.values()) {
            String name = subjectEnum.getSubjectName().toLowerCase();
            if (name.equals(subjectName.toLowerCase())) {
                return subjectEnum;
            }
        }
        return null;
    }
}
